﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ExportDeclarationsDetailDA : IExportDeclarationsDetailDA
    {
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string CASE_ID = "@CASE_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string CUBE = "@CUBE";
        private string DECLARATIONS_ID = "@DECLARATIONS_ID";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string GROSS_WGT = "@GROSS_WGT";
        private string ITEM_NO_DESCR = "@ITEM_NO_DESCR";
        private string ITEM_NO_ID = "@ITEM_NO_ID";
        private string LINE_ID = "@LINE_ID";
        private string NET_WGT = "@NET_WGT";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string OTHER_UNIT1 = "@OTHER_UNIT1";
        private string OTHER_UNIT2 = "@OTHER_UNIT2";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string QTY = "@QTY";
        private string REMARK = "@REMARK";
        private string SKU_DESCR = "@SKU_DESCR";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_EXPORT_DECLARATIONS_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@EXPORT_DECLARATIONS_DETAIL WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_EXPORT_DECLARATIONS_DETAIL = " DELETE FROM @_@EXPORT_DECLARATIONS_DETAIL WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_EXPORT_DECLARATIONS_DETAILS = " DELETE FROM @_@EXPORT_DECLARATIONS_DETAIL WHERE DECLARATIONS_ID = @DECLARATIONS_ID";
        private string SQL_INSERT_EXPORT_DECLARATIONS_DETAIL = " INSERT INTO @_@EXPORT_DECLARATIONS_DETAIL ( WH_ID, DECLARATIONS_ID, LINE_ID, ORDER_ID, ORDER_LINE_ID, OWNER_ID, SKU_ID, ALT_SKU_ID, CASE_ID, STATUS, UOM_ID, PACK_ID, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, QTY, ITEM_NO_ID) VALUES ( @WH_ID, @DECLARATIONS_ID, @LINE_ID, @ORDER_ID, @ORDER_LINE_ID, @OWNER_ID, @SKU_ID, @ALT_SKU_ID, @CASE_ID, @STATUS, @UOM_ID, @PACK_ID, @CUBE, @GROSS_WGT, @NET_WGT, @OTHER_UNIT1, @OTHER_UNIT2, @UNIT_PRICE, @EXTENDED_PRICE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @QTY, @ITEM_NO_ID)  ";
        private string SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL = " SELECT  EDD.ROWID, EDD.WH_ID, EDD.DECLARATIONS_ID, EDD.LINE_ID, EDD.ORDER_ID, EDD.ORDER_LINE_ID, EDD.OWNER_ID, EDD.SKU_ID, EDD.ALT_SKU_ID, EDD.CASE_ID, EDD.STATUS, EDD.UOM_ID, EDD.PACK_ID, EDD.CUBE, EDD.GROSS_WGT,EDD.NET_WGT, EDD.OTHER_UNIT1, EDD.OTHER_UNIT2, EDD.UNIT_PRICE, EDD.EXTENDED_PRICE, EDD.UDF1, EDD.UDF2, EDD.UDF3, EDD.UDF4,EDD.UDF5, EDD.UDF6, EDD.UDF7, EDD.UDF8, EDD.UDF9, EDD.UDF10, EDD.REMARK, EDD.TS, EDD.CREATED_BY, EDD.CREATED_DATE, EDD.UPDATED_BY, EDD.UPDATED_DATE, EDD.QTY,S.DESCR AS SKU_DESCR,ITEM.DESCR  AS ITEM_NO_DESCR,EDD.ITEM_NO_ID  FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD  LEFT JOIN @_@SKU S ON EDD.OWNER_ID=S.OWNER_ID AND EDD.SKU_ID=S.SKU_ID LEFT JOIN @_@ITEM_NO ITEM ON EDD.ITEM_NO_ID=ITEM.ITEM_NO_ID";
        private string SQL_UPDATE_EXPORT_DECLARATIONS_DETAIL = " UPDATE @_@EXPORT_DECLARATIONS_DETAIL SET WH_ID = @WH_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, CASE_ID = @CASE_ID, STATUS = @STATUS, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), QTY = @QTY, ITEM_NO_ID = @ITEM_NO_ID WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID  ";
        private string SQL_UPDATE_EXPORT_DECLARATIONS_DETAIL_STATUS = " UPDATE @_@EXPORT_DECLARATIONS_DETAIL SET  STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE DECLARATIONS_ID = @DECLARATIONS_ID  ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckExportDeclarationsDetailIDUnique(string exportDeclarationsDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsDetailID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_EXPORT_DECLARATIONS_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckExportDeclarationsDetailIDUnique(DataBase dataBase, DbTransaction tran, string exportDeclarationsDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsDetailID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_EXPORT_DECLARATIONS_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteExportDeclarationsDetail(List<string> exportDeclarationsDetailID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < exportDeclarationsDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_EXPORT_DECLARATIONS_DETAILS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteExportDeclarationsDetail(DataBase dataBase, DbTransaction tran, List<string> exportDeclarationsDetailID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < exportDeclarationsDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_EXPORT_DECLARATIONS_DETAILS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllExportDeclarationsDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllExportDeclarationsDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetail(DataBase dataBase, DbTransaction tran, string declarationsID, string skuID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT EDD.ROWID, EDD.WH_ID, EDD.DECLARATIONS_ID, EDD.LINE_ID, EDD.ORDER_ID, EDD.ORDER_LINE_ID, EDD.OWNER_ID, EDD.SKU_ID, EDD.ALT_SKU_ID, EDD.CASE_ID, EDD.STATUS, EDD.UOM_ID, EDD.PACK_ID, EDD.CUBE, EDD.GROSS_WGT,EDD.NET_WGT, EDD.OTHER_UNIT1, EDD.OTHER_UNIT2, EDD.UNIT_PRICE, EDD.EXTENDED_PRICE, EDD.UDF1, EDD.UDF2, EDD.UDF3, EDD.UDF4,EDD.UDF5, EDD.UDF6, EDD.UDF7, EDD.UDF8, EDD.UDF9, EDD.UDF10, EDD.REMARK, EDD.TS, EDD.CREATED_BY, EDD.CREATED_DATE, EDD.UPDATED_BY, EDD.UPDATED_DATE, EDD.QTY,EDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD ");
            strSql.Append("    WHERE EDD.DECLARATIONS_ID = @DECLARATIONS_ID AND EDD.SKU_ID=@SKU_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", declarationsID), new SqlParameter("@SKU_ID", skuID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                exportDeclarationsDetailList.Add(exportDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsDetailList;
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetailByID(string exportdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT EDD.ROWID, EDD.WH_ID, EDD.DECLARATIONS_ID, EDD.LINE_ID, EDD.ORDER_ID, EDD.ORDER_LINE_ID, EDD.OWNER_ID, EDD.SKU_ID, EDD.ALT_SKU_ID, EDD.CASE_ID, EDD.STATUS, EDD.UOM_ID, EDD.PACK_ID, EDD.CUBE, EDD.GROSS_WGT,EDD.NET_WGT, EDD.OTHER_UNIT1, EDD.OTHER_UNIT2, EDD.UNIT_PRICE, EDD.EXTENDED_PRICE, EDD.UDF1, EDD.UDF2, EDD.UDF3, EDD.UDF4,EDD.UDF5, EDD.UDF6, EDD.UDF7, EDD.UDF8, EDD.UDF9, EDD.UDF10, EDD.REMARK, EDD.TS, EDD.CREATED_BY, EDD.CREATED_DATE, EDD.UPDATED_BY, EDD.UPDATED_DATE, EDD.QTY,EDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD ");
            strSql.Append("    WHERE EDD.DECLARATIONS_ID = @DECLARATIONS_ID");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsdetailID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                    exportDeclarationsDetailList.Add(exportDeclarationsDetailInfo);
                }
            }
            return exportDeclarationsDetailList;
        }

        public ExportDeclarationsDetailInfo GetExportDeclarationsDetailByID(string exportdeclarationsdetailID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID) + " WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsdetailID), new SqlParameter("@LINE_ID", lineID) };
            ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ITEM_NO_DESCR"].ToString(), (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                }
            }
            return exportDeclarationsDetailInfo;
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetailByID(DataBase dataBase, DbTransaction tran, string exportdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT EDD.ROWID, EDD.WH_ID, EDD.DECLARATIONS_ID, EDD.LINE_ID, EDD.ORDER_ID, EDD.ORDER_LINE_ID, EDD.OWNER_ID, EDD.SKU_ID, EDD.ALT_SKU_ID, EDD.CASE_ID, EDD.STATUS, EDD.UOM_ID, EDD.PACK_ID, EDD.CUBE, EDD.GROSS_WGT,EDD.NET_WGT, EDD.OTHER_UNIT1, EDD.OTHER_UNIT2, EDD.UNIT_PRICE, EDD.EXTENDED_PRICE, EDD.UDF1, EDD.UDF2, EDD.UDF3, EDD.UDF4,EDD.UDF5, EDD.UDF6, EDD.UDF7, EDD.UDF8, EDD.UDF9, EDD.UDF10, EDD.REMARK, EDD.TS, EDD.CREATED_BY, EDD.CREATED_DATE, EDD.UPDATED_BY, EDD.UPDATED_DATE, EDD.QTY,EDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD ");
            strSql.Append("    WHERE EDD.DECLARATIONS_ID = @DECLARATIONS_ID");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsdetailID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                exportDeclarationsDetailList.Add(exportDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsDetailList;
        }

        public ExportDeclarationsDetailInfo GetExportDeclarationsDetailByID(DataBase dataBase, DbTransaction tran, string exportdeclarationsdetailID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID) + " WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsdetailID), new SqlParameter("@LINE_ID", lineID) };
            ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ITEM_NO_DESCR"].ToString(), (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsDetailInfo;
        }

        public DataSet GetExportDeclarationsDetailByQueryList(List<string> sqlWhere, ExportDeclarationsDetailQueryEntity exportdeclarationsdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!exportdeclarationsdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, exportdeclarationsdetailQuery.CurrentPage, exportdeclarationsdetailQuery.PageSize, exportdeclarationsdetailQuery.SortField, exportdeclarationsdetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetExportDeclarationsDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ExportDeclarationsDetailQueryEntity exportdeclarationsdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, exportdeclarationsdetailQuery.CurrentPage, exportdeclarationsdetailQuery.PageSize, exportdeclarationsdetailQuery.SortField, exportdeclarationsdetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetailGroupBy(string exportdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT EDD.DECLARATIONS_ID, EDD.OWNER_ID, EDD.SKU_ID,EDD.UOM_ID,SUM(CONVERT(FLOAT,ISNULL(EDD.UDF1,'0'))) AS UDF1,SUM(CONVERT(FLOAT,ISNULL(EDD.UDF2,'0'))) AS UDF2, SUM(EDD.CUBE) AS CUBE , SUM(EDD.GROSS_WGT) AS GROSS_WGT,SUM(EDD.NET_WGT) AS  NET_WGT,EDD.UNIT_PRICE, SUM(EDD.EXTENDED_PRICE) AS EXTENDED_PRICE,  EDD.REMARK,SUM(EDD.QTY) AS QTY,S.DESCR AS SKU_DESCR,ITEM.DESCR  AS ITEM_NO_DESCR,EDD.ITEM_NO_ID,SUM(OD.SHIPPED_QTY) AS SHIPPED_QTY,SUM(OD.SHIPPED_QTY-ISNULL(OD.DECLARATION_QTY,0)) AS DIFFERENCES_QTY  ");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD  LEFT JOIN @_@ORDER_DETAIL OD ON EDD.ORDER_ID =OD.ORDER_ID AND EDD.ORDER_LINE_ID=OD.LINE_ID ");
            strSql.Append("   LEFT JOIN @_@SKU S ON EDD.OWNER_ID=S.OWNER_ID AND EDD.SKU_ID=S.SKU_ID LEFT JOIN  @_@ITEM_NO ITEM ON EDD.ITEM_NO_ID=ITEM.ITEM_NO_ID   WHERE EDD.DECLARATIONS_ID = @DECLARATIONS_ID");
            strSql.Append("   GROUP BY EDD.DECLARATIONS_ID, EDD.OWNER_ID, EDD.SKU_ID,EDD.UOM_ID,EDD.UNIT_PRICE, EDD.REMARK,S.DESCR ,ITEM.DESCR,EDD.ITEM_NO_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsdetailID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002 = new DateTime();
                    CS00002 = new DateTime();
                    ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo(0, "", reader["DECLARATIONS_ID"].ToString(), 0, "", 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), "", "", "", reader["UOM_ID"].ToString(), "", (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, 0M, 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), "", "", "", "", "", "", "", "", reader["REMARK"].ToString(), new byte[0], "", CS00002, "", CS00002, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ITEM_NO_DESCR"].ToString(), (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                    exportDeclarationsDetailList.Add(exportDeclarationsDetailInfo);
                }
            }
            return exportDeclarationsDetailList;
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetailGroupBy(DataBase dataBase, DbTransaction tran, string exportdeclarationsdetailID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT EDD.DECLARATIONS_ID, EDD.OWNER_ID, EDD.SKU_ID,EDD.UOM_ID,SUM(CONVERT(FLOAT,ISNULL(EDD.UDF1,'0'))) AS UDF1,SUM(CONVERT(FLOAT,ISNULL(EDD.UDF2,'0'))) AS UDF2, SUM(EDD.CUBE) AS CUBE , SUM(EDD.GROSS_WGT) AS GROSS_WGT,SUM(EDD.NET_WGT) AS  NET_WGT,EDD.UNIT_PRICE, SUM(EDD.EXTENDED_PRICE) AS EXTENDED_PRICE,  EDD.REMARK,SUM(EDD.QTY) AS QTY,S.DESCR AS SKU_DESCR,ITEM.DESCR  AS ITEM_NO_DESCR,EDD.ITEM_NO_ID,SUM(OD.SHIPPED_QTY) AS SHIPPED_QTY,SUM(OD.SHIPPED_QTY-ISNULL(OD.DECLARATION_QTY,0)) AS DIFFERENCES_QTY  ");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD  LEFT JOIN @_@ORDER_DETAIL OD ON EDD.ORDER_ID =OD.ORDER_ID AND EDD.ORDER_LINE_ID=OD.LINE_ID ");
            strSql.Append("   LEFT JOIN @_@SKU S ON EDD.OWNER_ID=S.OWNER_ID AND EDD.SKU_ID=S.SKU_ID LEFT JOIN  @_@ITEM_NO ITEM ON EDD.ITEM_NO_ID=ITEM.ITEM_NO_ID   WHERE EDD.DECLARATIONS_ID = @DECLARATIONS_ID");
            strSql.Append("   GROUP BY EDD.DECLARATIONS_ID, EDD.OWNER_ID, EDD.SKU_ID,EDD.UOM_ID,EDD.UNIT_PRICE, EDD.REMARK,S.DESCR ,ITEM.DESCR,EDD.ITEM_NO_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportdeclarationsdetailID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002 = new DateTime();
                CS00002 = new DateTime();
                ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo(0, "", reader["DECLARATIONS_ID"].ToString(), 0, "", 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), "", "", "", reader["UOM_ID"].ToString(), "", (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, 0M, 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), "", "", "", "", "", "", "", "", reader["REMARK"].ToString(), new byte[0], "", CS00002, "", CS00002, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), reader["SKU_DESCR"].ToString(), reader["ITEM_NO_DESCR"].ToString(), (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                exportDeclarationsDetailList.Add(exportDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsDetailList;
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetailInfoByRotationRule(DataBase dataBase, DbTransaction tran, ExportDeclarationsDetailInfo exportDeclarationsDetailInfo, string orderby, string whLogin)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT  EDD.ROWID, EDD.WH_ID, EDD.DECLARATIONS_ID, EDD.LINE_ID, EDD.ORDER_ID, EDD.ORDER_LINE_ID, EDD.OWNER_ID, EDD.SKU_ID, EDD.ALT_SKU_ID, EDD.CASE_ID, EDD.STATUS, EDD.UOM_ID, EDD.PACK_ID, EDD.CUBE, EDD.GROSS_WGT,EDD.NET_WGT, EDD.OTHER_UNIT1, EDD.OTHER_UNIT2, EDD.UNIT_PRICE, EDD.EXTENDED_PRICE, EDD.UDF1, EDD.UDF2, EDD.UDF3, EDD.UDF4,EDD.UDF5, EDD.UDF6, EDD.UDF7, EDD.UDF8, EDD.UDF9, EDD.UDF10, EDD.REMARK, EDD.TS, EDD.CREATED_BY, EDD.CREATED_DATE, EDD.UPDATED_BY, EDD.UPDATED_DATE, EDD.QTY,EDD.ITEM_NO_ID,OD.SHIPPED_QTY,(OD.SHIPPED_QTY-ISNULL(OD.DECLARATION_QTY,0)) AS DIFFERENCES_QTY ");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD  ");
            strSql.Append("   LEFT JOIN @_@ORDER_DETAIL OD ON EDD.ORDER_ID =OD.ORDER_ID AND EDD.ORDER_LINE_ID=OD.LINE_ID   LEFT JOIN  @_@INV_TRANS IT  ON EDD.ORDER_ID=IT.DOC_ID AND EDD.ORDER_LINE_ID=IT.DOC_LINE_ID AND  IT.TRANS_TYPE='OT' AND IT.SOURCE_TRANS_TYPE='PickingUpdate' AND IT.STATUS='OK' ");
            strSql.Append("   WHERE EDD.DECLARATIONS_ID = @DECLARATIONS_ID AND EDD.OWNER_ID=@OWNER_ID AND EDD.SKU_ID=@SKU_ID AND EDD.UOM_ID=@UOM_ID AND EDD.UNIT_PRICE= @UNIT_PRICE  ");
            if (string.IsNullOrEmpty(exportDeclarationsDetailInfo.Remark))
            {
                strSql.Append("   AND (EDD.REMARK IS NULL OR EDD.REMARK = '') ");
            }
            else
            {
                strSql.Append("   AND EDD.REMARK  =@REMARK");
            }
            if (string.IsNullOrEmpty(exportDeclarationsDetailInfo.ItemNoID))
            {
                strSql.Append("   AND (EDD.ITEM_NO_ID IS NULL OR EDD.ITEM_NO_ID = '') ");
            }
            else
            {
                strSql.Append("   AND EDD.ITEM_NO_ID  =@ITEM_NO_ID");
            }
            strSql.AppendFormat("  {0}", orderby);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", exportDeclarationsDetailInfo.DeclarationsID), new SqlParameter("@OWNER_ID", exportDeclarationsDetailInfo.OwnerID), new SqlParameter("@SKU_ID", exportDeclarationsDetailInfo.SkuID), new SqlParameter("@UOM_ID", exportDeclarationsDetailInfo.UomID), new SqlParameter("@UNIT_PRICE", exportDeclarationsDetailInfo.UnitPrice), new SqlParameter("@REMARK", exportDeclarationsDetailInfo.Remark), new SqlParameter("@ITEM_NO_ID", exportDeclarationsDetailInfo.ItemNoID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLogin).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00003;
                ExportDeclarationsDetailInfo exportDeclarationsDetailsInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["ITEM_NO_ID"].ToString(), "", "", (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["DIFFERENCES_QTY"].ToString() != "") ? decimal.Parse(reader["DIFFERENCES_QTY"].ToString()) : 0M);
                exportDeclarationsDetailList.Add(exportDeclarationsDetailsInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsDetailList;
        }

        public List<ExportDeclarationsDetailInfo> GetExportDeclarationsDetailOrderIDLineID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("   SELECT EDD.ROWID, EDD.WH_ID, EDD.DECLARATIONS_ID, EDD.LINE_ID, EDD.ORDER_ID, EDD.ORDER_LINE_ID, EDD.OWNER_ID, EDD.SKU_ID, EDD.ALT_SKU_ID, EDD.CASE_ID, EDD.STATUS, EDD.UOM_ID, EDD.PACK_ID, EDD.CUBE, EDD.GROSS_WGT,EDD.NET_WGT, EDD.OTHER_UNIT1, EDD.OTHER_UNIT2, EDD.UNIT_PRICE, EDD.EXTENDED_PRICE, EDD.UDF1, EDD.UDF2, EDD.UDF3, EDD.UDF4,EDD.UDF5, EDD.UDF6, EDD.UDF7, EDD.UDF8, EDD.UDF9, EDD.UDF10, EDD.REMARK, EDD.TS, EDD.CREATED_BY, EDD.CREATED_DATE, EDD.UPDATED_BY, EDD.UPDATED_DATE, EDD.QTY,EDD.ITEM_NO_ID");
            strSql.Append("   FROM  @_@EXPORT_DECLARATIONS_DETAIL EDD ");
            strSql.Append("    WHERE EDD.ORDER_ID = @ORDER_ID AND EDD.ORDER_LINE_ID=@ORDER_LINE_ID ");
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", lineID) };
            List<ExportDeclarationsDetailInfo> exportDeclarationsDetailList = new List<ExportDeclarationsDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql.Replace("@_@", whLoginID).ToString(), paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ExportDeclarationsDetailInfo exportDeclarationsDetailInfo = new ExportDeclarationsDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["DECLARATIONS_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["CUBE"].ToString() != "") ? decimal.Parse(reader["CUBE"].ToString()) : 0M, (reader["GROSS_WGT"].ToString() != "") ? decimal.Parse(reader["GROSS_WGT"].ToString()) : 0M, (reader["NET_WGT"].ToString() != "") ? decimal.Parse(reader["NET_WGT"].ToString()) : 0M, (reader["OTHER_UNIT1"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT1"].ToString()) : 0M, (reader["OTHER_UNIT2"].ToString() != "") ? decimal.Parse(reader["OTHER_UNIT2"].ToString()) : 0M, (reader["UNIT_PRICE"].ToString() != "") ? decimal.Parse(reader["UNIT_PRICE"].ToString()) : 0M, (reader["EXTENDED_PRICE"].ToString() != "") ? decimal.Parse(reader["EXTENDED_PRICE"].ToString()) : 0M, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, "", "", "", 0M, 0M);
                exportDeclarationsDetailList.Add(exportDeclarationsDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return exportDeclarationsDetailList;
        }

        public int InsertExportDeclarationsDetail(ExportDeclarationsDetailInfo exportDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                exportDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarationsDetail_Parameters(exportDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertExportDeclarationsDetail(DataBase dataBase, DbTransaction tran, ExportDeclarationsDetailInfo exportDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                exportDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarationsDetail_Parameters(exportDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_ExportDeclarationsDetail_Parameters(ExportDeclarationsDetailInfo exportDeclarationsDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DECLARATIONS_ID, SqlDbType.VarChar, 30), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CUBE, SqlDbType.Decimal, 13), new SqlParameter(this.GROSS_WGT, SqlDbType.Decimal, 13), new SqlParameter(this.NET_WGT, SqlDbType.Decimal, 13), new SqlParameter(this.OTHER_UNIT1, SqlDbType.Decimal, 13), 
                new SqlParameter(this.OTHER_UNIT2, SqlDbType.Decimal, 13), new SqlParameter(this.UNIT_PRICE, SqlDbType.Decimal, 13), new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Decimal, 13), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), 
                new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ITEM_NO_ID, SqlDbType.VarChar, 50)
             };
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.WhID))
            {
                paramArray[0].Value = exportDeclarationsDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = exportDeclarationsDetailInfo.DeclarationsID;
            paramArray[2].Value = exportDeclarationsDetailInfo.LineID;
            paramArray[3].Value = exportDeclarationsDetailInfo.OrderID;
            paramArray[4].Value = exportDeclarationsDetailInfo.OrderLineID;
            paramArray[5].Value = exportDeclarationsDetailInfo.OwnerID;
            paramArray[6].Value = exportDeclarationsDetailInfo.SkuID;
            paramArray[7].Value = exportDeclarationsDetailInfo.AltSkuID;
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.CaseID))
            {
                paramArray[8].Value = exportDeclarationsDetailInfo.CaseID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = exportDeclarationsDetailInfo.Status;
            paramArray[10].Value = exportDeclarationsDetailInfo.UomID;
            paramArray[11].Value = exportDeclarationsDetailInfo.PackID;
            paramArray[12].Value = exportDeclarationsDetailInfo.Cube;
            paramArray[13].Value = exportDeclarationsDetailInfo.GrossWgt;
            paramArray[14].Value = exportDeclarationsDetailInfo.NetWgt;
            paramArray[15].Value = exportDeclarationsDetailInfo.OtherUnit1;
            paramArray[0x10].Value = exportDeclarationsDetailInfo.OtherUnit2;
            paramArray[0x11].Value = exportDeclarationsDetailInfo.UnitPrice;
            paramArray[0x12].Value = exportDeclarationsDetailInfo.ExtendedPrice;
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf1))
            {
                paramArray[0x13].Value = exportDeclarationsDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf2))
            {
                paramArray[20].Value = exportDeclarationsDetailInfo.Udf2;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf3))
            {
                paramArray[0x15].Value = exportDeclarationsDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf4))
            {
                paramArray[0x16].Value = exportDeclarationsDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf5))
            {
                paramArray[0x17].Value = exportDeclarationsDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf6))
            {
                paramArray[0x18].Value = exportDeclarationsDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf7))
            {
                paramArray[0x19].Value = exportDeclarationsDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf8))
            {
                paramArray[0x1a].Value = exportDeclarationsDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf9))
            {
                paramArray[0x1b].Value = exportDeclarationsDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Udf10))
            {
                paramArray[0x1c].Value = exportDeclarationsDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.Remark))
            {
                paramArray[0x1d].Value = exportDeclarationsDetailInfo.Remark;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = exportDeclarationsDetailInfo.CreatedBy;
            paramArray[0x1f].Value = exportDeclarationsDetailInfo.UpdatedBy;
            paramArray[0x20].Value = exportDeclarationsDetailInfo.Qty;
            if (!string.IsNullOrEmpty(exportDeclarationsDetailInfo.ItemNoID))
            {
                paramArray[0x21].Value = exportDeclarationsDetailInfo.ItemNoID;
                return paramArray;
            }
            paramArray[0x21].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateExportDeclarationsDetail(ExportDeclarationsDetailInfo exportDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                exportDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarationsDetail_Parameters(exportDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateExportDeclarationsDetail(DataBase dataBase, DbTransaction tran, ExportDeclarationsDetailInfo exportDeclarationsDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                exportDeclarationsDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ExportDeclarationsDetail_Parameters(exportDeclarationsDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateExportDeclarationsDetailByDeclarationsID(DataBase dataBase, DbTransaction tran, ExportDeclarationsDetailInfo info, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = " UPDATE @_@EXPORT_DECLARATIONS_DETAIL SET UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, CUBE = @CUBE, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, UDF1 = @UDF1, UDF2 = @UDF2 WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND SKU_ID = @SKU_ID AND LINE_ID=@LINE_ID";
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", info.DeclarationsID), new SqlParameter("@LINE_ID", info.LineID), new SqlParameter("@SKU_ID", info.SkuID), new SqlParameter("@GROSS_WGT", info.GrossWgt), new SqlParameter("@NET_WGT", info.NetWgt), new SqlParameter("@CUBE", info.Cube), new SqlParameter("@UNIT_PRICE", info.UnitPrice), new SqlParameter("@EXTENDED_PRICE", info.ExtendedPrice), new SqlParameter("@UDF1", info.Udf1), new SqlParameter("@UDF2", info.Udf2), new SqlParameter("@UPDATED_BY", updateBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateExportDeclarationsDetailQtyByDeclarationsID(DataBase dataBase, DbTransaction tran, string DeclarationsID, string LineID, decimal qty, decimal extendedPrice, string updateBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = " UPDATE @_@EXPORT_DECLARATIONS_DETAIL SET UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), QTY = @QTY,EXTENDED_PRICE = @EXTENDED_PRICE WHERE DECLARATIONS_ID = @DECLARATIONS_ID AND LINE_ID = @LINE_ID ";
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", DeclarationsID), new SqlParameter("@LINE_ID", LineID), new SqlParameter("@QTY", qty), new SqlParameter("@EXTENDED_PRICE", extendedPrice), new SqlParameter("@UPDATED_BY", updateBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateExportDeclarationsDetailStatusByID(DataBase dataBase, DbTransaction tran, string declarationsID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONS_ID", declarationsID), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_EXPORT_DECLARATIONS_DETAIL_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

